package br.com.fiap.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import br.com.fiap.ConnectionFactory.ConnectionFactory;
import br.com.fiap.bean.Agencia;
import br.com.fiap.bean.Banco;

public class AgenciaDAO {
	
	public ArrayList<Agencia> getAll() throws SQLException, ClassNotFoundException{
		
		ArrayList<Agencia> agencias = new ArrayList<Agencia>();
		
		Connection conn = ConnectionFactory.getConnection();
		
		String sql = "SELECT id, nome, banco FROM BANCO.AGENCIA ORDER BY id";
		
		PreparedStatement stmt = conn.prepareStatement(sql);
		
		ResultSet rs = stmt.executeQuery();
		
		while(rs.next()){
			
			Agencia a = new Agencia();
			a.setNumero(rs.getInt("id"));
			a.setNome(rs.getString("nome"));
			Banco banco =  new Banco();
			banco.setNumero(rs.getInt("banco"));
			a.setBanco(banco);
			
			agencias.add(a);
		}

		conn.close();
		
		return agencias;
	}
	
	
		public Agencia getUnique(int numero, int numbco) throws SQLException, ClassNotFoundException{
			
			Agencia agencia = new Agencia();
			
			Connection conn = ConnectionFactory.getConnection();
			
			String sql = "SELECT id, nome, banco  FROM BANCO.AGENCIA WHERE id = ? and banco = ? ";
			
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setInt(1, numero);
			stmt.setInt(2, numbco);
						
			ResultSet rs = stmt.executeQuery();
			
			if(rs.next()){
				
				agencia.setNumero(rs.getInt("id"));
				agencia.setNome(rs.getString("nome"));
				Banco banco =  new Banco();
				banco.setNumero(rs.getInt("banco"));
				agencia.setBanco(banco);
			}

			conn.close();
			
			return agencia;
		}
		
	}

